﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Data;
using System.Collections;

namespace FOBTV.Web.DBUtility
{
	/// <summary>
	/// 查询数据库的方法类
	/// </summary>
	internal class SelectMethod : BaseMethod
	{
		private static Hashtable SelectClauseStore = new Hashtable();
		public SelectMethod(Type infoType)
			: base(infoType)
		{
		}
		public SelectMethod(BaseInfo info)
			: base(info)
		{
		}
		public SelectMethod(string TableName, BaseInfo info)
			: base(TableName, info)
		{
		}

		public DataTable Select(string WhereClause, string Order)
		{
			return SelectParam(WhereClause, Order, null);
		}

		public DataTable SelectParam(string WhereClause, string Order, IDataParameter[] paramaters)
		{
			if (base.InfoType != null)
			{
				StringBuilder sb_sql = new StringBuilder(this.SelectClause);
				if (!string.IsNullOrEmpty(WhereClause))
				{
					sb_sql.AppendFormat(" WHERE {0}", WhereClause);
				}
				if (!string.IsNullOrEmpty(Order))
				{
					sb_sql.AppendFormat(" ORDER BY {0}", Order);
				}
				sb_sql.Append(';');
				string CommandText = sb_sql.ToString();
				DataTable result = base.ExecuteDataTable(CommandText, paramaters);
				return result;
			}
			return null;
		}

		public DataTable SelectTop(int TopRows)
		{
			return SelectTop(TopRows, null, null);
		}

		/// <summary>
		/// 查询前n行记录
		/// </summary>
		/// <param name="TopRows"></param>
		/// <param name="WhereClause"></param>
		/// <param name="Order"></param>
		/// <returns></returns>
		public DataTable SelectTop(int TopRows, string WhereClause, string Order)
		{
			if (base.InfoType != null)
			{
				StringBuilder sb_selecttop = new StringBuilder();
				string TopClause = TopRows > 0 ? string.Format("TOP {0}", TopRows) : null;
				SelectCommandText(sb_selecttop, TopClause);
				if (!string.IsNullOrEmpty(WhereClause))
				{
					sb_selecttop.AppendFormat(" WHERE {0}", WhereClause);
				}
				if (!string.IsNullOrEmpty(Order))
				{
					sb_selecttop.AppendFormat(" ORDER BY {0}", Order);
				}
				sb_selecttop.Append(';');
				string CommandText = sb_selecttop.ToString();
				DataTable result = base.ExecuteDataTable(CommandText, null);
				return result;
			}
			return null;
		}

		public DataRow SelectByIdentity()
		{
			if (!((base.Entity != null) && base.HasIdentity))
			{
				return null;
			}
			DataTable table = this.Select(base.IdentityWhereClause, null);
			if (table == null || table.Rows.Count <= 0)
			{
				return null;
			}
			return table.Rows[0];
		}

		public bool SelectInfoByIdentity()
		{
			DataRow row = SelectByIdentity();
			Hashtable table = base.GetHashTableByDatarow(row);
			return base.Entity.SetInternalData(table);
		}

		/// <summary>
		/// 针对一条记录的查询，需传入一个BaseInfo对象
		/// </summary>
		/// <param name="WhereClause"></param>
		/// <returns></returns>
		public bool SingleSelect(string WhereClause)
		{
			return SingleSelect(WhereClause, null);
		}

		/// <summary>
		/// 针对一条记录的查询，需传入一个BaseInfo对象
		/// </summary>
		/// <param name="WhereClause"></param>
		/// <returns></returns>
		public bool SingleSelect(string WhereClause, IDataParameter[] parameters)
		{
			if (base.Entity == null)
			{
				return false;
			}
			try
			{
				if (WhereClause == null)
				{
					WhereClause = base.IdentityWhereClause;
				}
				DataTable result = this.SelectParam(WhereClause, null, parameters);
				if (result == null || result.Rows.Count <= 0)
				{
					return false;
				}
				DataRow row = result.Rows[0];
				Hashtable table = base.GetHashTableByDatarow(row);
				return base.Entity.SetInternalData(table);
			}
			catch (Exception ex)
			{
				return false;
			}
		}

		/// <summary>
		/// 分页
		/// </summary>
		/// <param name="pe"></param>
		/// <returns></returns>
		public DataTable SelectPaging(PagingEntity pe)
		{
			if (pe.RecordCount <= 0)
			{
				CountMethod count = new CountMethod(base.InfoType);
				pe.RecordCount = count.Count(pe.WhereClause);//SqlHelper.GetRecordCount(pe.TableName, pe.WhereClause);
			}
			int startRecord = (pe.PageIndex - 1) * pe.PageSize;
			int endRecord = pe.PageSize + startRecord;
			int outerTop = Math.Min(pe.PageSize, pe.RecordCount - startRecord);
			int innerTop = outerTop + startRecord;
			StringBuilder sb_paging = new StringBuilder();
			sb_paging.AppendFormat("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2} FROM [{3}]", new object[] { outerTop, innerTop, pe.SelectString, pe.TableName });
			if (!string.IsNullOrEmpty(pe.WhereClause))
			{
				sb_paging.AppendFormat(" where {0}", pe.WhereClause);
			}
			sb_paging.AppendFormat(" ORDER BY {0}) AS inner_table ORDER BY {1}) AS final_table ORDER BY {0}", pe.AscString, pe.DescString);
			DataTable tb = base.ExecuteDataTable(sb_paging.ToString(), null);//SqlHelper.SelectToDataTable(sb_paging.ToString());
			return tb;
		}

		protected string SelectCommandText(StringBuilder sb_select, string TopClause)
		{
			sb_select.Append("SELECT ");
			if (!string.IsNullOrEmpty(TopClause))
			{
				sb_select.AppendFormat("{0} ", TopClause);
			}
			base.AppendParameterString(sb_select, "[{0}]", ", ", true);
			sb_select.AppendFormat(" FROM [{0}]", base.TableName);
			return sb_select.ToString();
		}
		/// <summary>
		/// select 语句前部
		/// </summary>
		public string SelectClause
		{
			get
			{
				string result = SelectClauseStore[base.TableName] as string;
				if (result == null)
				{
					StringBuilder sb_select = new StringBuilder();
					this.SelectCommandText(sb_select, null);
					SelectClauseStore[base.TableName] = result = sb_select.ToString();
				}
				return SelectClauseStore[base.TableName].ToString();
			}
		}
	}
}